﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BasicCMS.Data
{
    public class QueryTableBuilder
    {
        private readonly IUnitOfWork unitOfWork;
        private IList<KeyValuePair<string, object>> parameters = new List<KeyValuePair<string, object>>();
        private StringBuilder orderBuilder = new StringBuilder();
        private string tableName;
        private string condition;
        private IDbCommand command;

        public QueryTableBuilder(IUnitOfWork unitOfWork, string tableName)
        {
            this.unitOfWork = unitOfWork;
            this.tableName = tableName;
        }

        public IDbCommand Command
        {
            set { command = value; }
        }

        public QueryTableBuilder Where(string condition)
        {
            this.condition = condition;
            return this;
        }

        public QueryTableBuilder Param(string name, object value)
        {
            parameters.Add(new KeyValuePair<string, object>(name, value));
            return this;
        }

        public QueryTableBuilder OrderBy(string field)
        {
            orderBuilder.AppendFormat("[{0}] ASC,", field);
            return this;
        }

        public QueryTableBuilder OrderByDescending(string field)
        {
            orderBuilder.AppendFormat("[{0}] DESC,", field);
            return this;
        }


        public PagedTable ToPagedTable(int pageIndex,int pageSize)
        {
            string sqlWithOutSelect = String.Format(" * FROM [{0}]", tableName);
            string fromSql = String.Format("FROM [{0}]", tableName);
            var result = new PagedTable();
            result.CurrentPageIndex = pageIndex;
            result.PageSize = pageSize;

            orderBuilder.TrimEnd();
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.AppendFormat("SELECT * FROM(SELECT ROW_NUMBER() OVER(ORDER BY {0}) N,{1}) T WHERE T.N >={2} AND T.N<={3}", orderBuilder, sqlWithOutSelect, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize);
            sqlBuilder.AppendLine(";");
            sqlBuilder.AppendFormat("SELECT COUNT(1) {0}", fromSql);

            DataSet ds = new DataSet();
            command.CommandText = sqlBuilder.ToString();
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                sda.SelectCommand = (SqlCommand)command;
                sda.Fill(ds);
            }

            int count = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
            result.TotalItemCount = count;
            result.Table = ds.Tables[0];

            return result;
        }

        public DataRow ToRow()
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.AppendFormat("SELECT * FROM [{0}] ", tableName);
            if (!String.IsNullOrEmpty(condition))
            {
                sqlBuilder.AppendFormat(" WHERE {0}", condition);
            }
            if (orderBuilder.Length > 0)
            {
                sqlBuilder.AppendFormat("ORDER BY {0}", orderBuilder);
            }
            DataTable dt = new DataTable();
            command.CommandText = sqlBuilder.ToString();
            foreach (var kvp in parameters)
            {
                command.Parameters.Add(new SqlParameter("@" + kvp.Key, kvp.Value));
            }
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                sda.SelectCommand = (SqlCommand)command;
                sda.Fill(dt);
            }

            return dt.Rows.Count > 0 ? dt.Rows[0] : null;
        }

    }
}
